const express = require('express');
const router = express.Router();
const pool = require('../config/db');

// 获取当天的基础信息
router.get('/basic-info/today', async (req, res) => {
  try {
    const today = new Date().toISOString().split('T')[0];
    const [rows] = await pool.execute(
      'SELECT * FROM `基础信息` WHERE `评测日期` = ?',
      [today]
    );
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching basic info:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期、牌别、批次获取日期选项（去重）- 必须在更通用的路由之前
router.get('/basic-info/:evaluationDate/:brand/:batch/dates', async (req, res) => {
  try {
    const { evaluationDate, brand, batch } = req.params;
    const [rows] = await pool.execute(
      'SELECT DISTINCT `日期` FROM `基础信息` WHERE `评测日期` = ? AND `牌别` = ? AND `批次` = ? ORDER BY `日期`',
      [evaluationDate, brand, batch]
    );
    res.json({ success: true, data: rows.map(row => row.日期) });
  } catch (error) {
    console.error('Error fetching dates:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期和牌别获取批次选项（去重）- 必须在更通用的路由之前
router.get('/basic-info/:evaluationDate/:brand/batches', async (req, res) => {
  try {
    const { evaluationDate, brand } = req.params;
    const [rows] = await pool.execute(
      'SELECT DISTINCT `批次` FROM `基础信息` WHERE `评测日期` = ? AND `牌别` = ? ORDER BY `批次`',
      [evaluationDate, brand]
    );
    res.json({ success: true, data: rows.map(row => row.批次) });
  } catch (error) {
    console.error('Error fetching batches:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期获取牌别选项（去重）- 必须在更通用的路由之前
router.get('/basic-info/:evaluationDate/brands', async (req, res) => {
  try {
    const { evaluationDate } = req.params;
    const [rows] = await pool.execute(
      'SELECT DISTINCT `牌别` FROM `基础信息` WHERE `评测日期` = ? ORDER BY `牌别`',
      [evaluationDate]
    );
    res.json({ success: true, data: rows.map(row => row.牌别) });
  } catch (error) {
    console.error('Error fetching brands:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期获取基础信息列表 - 必须在所有更具体的路由之后
router.get('/basic-info/:evaluationDate', async (req, res) => {
  try {
    const { evaluationDate } = req.params;
    const [rows] = await pool.execute(
      'SELECT * FROM `基础信息` WHERE `评测日期` = ? ORDER BY `牌别`, `批次`',
      [evaluationDate]
    );
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching basic info by date:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 创建基础信息（支持创建或更新）
router.post('/basic-info', async (req, res) => {
  try {
    const { 评测日期, 牌别, 批次, 日期 } = req.body;
    const final评测日期 = 评测日期 || new Date().toISOString().split('T')[0];

    // 先检查基础信息是否存在（使用完整主键）
    const [existing] = await pool.execute(
      'SELECT * FROM `基础信息` WHERE `评测日期` = ? AND `牌别` = ? AND `批次` = ?',
      [final评测日期, 牌别, 批次]
    );

    if (existing.length > 0) {
      // 更新基础信息表
      await pool.execute(
        'UPDATE `基础信息` SET `日期` = ? WHERE `评测日期` = ? AND `牌别` = ? AND `批次` = ?',
        [日期, final评测日期, 牌别, 批次]
      );

      // 更新评价表的基础信息部分
      await pool.execute(
        'UPDATE `评价表` SET `日期` = ? WHERE `评测日期` = ? AND `牌别` = ? AND `批次` = ?',
        [日期, final评测日期, 牌别, 批次]
      );

      res.json({ success: true, message: '基础信息更新成功' });
    } else {
      // 插入基础信息表
      await pool.execute(
        'INSERT INTO `基础信息` (`评测日期`, `牌别`, `批次`, `日期`) VALUES (?, ?, ?, ?)',
        [final评测日期, 牌别, 批次, 日期]
      );

      // 同时插入评价表，其他字段保持默认值
      await pool.execute(
        'INSERT INTO `评价表` (`评测日期`, `牌别`, `批次`, `日期`) VALUES (?, ?, ?, ?)',
        [final评测日期, 牌别, 批次, 日期]
      );

      res.json({ success: true, message: '基础信息创建成功' });
    }
  } catch (error) {
    console.error('Error creating basic info:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 获取当天的评价信息
router.get('/evaluation/today', async (req, res) => {
  try {
    const today = new Date().toISOString().split('T')[0];
    const [rows] = await pool.execute(
      'SELECT * FROM `评价表` WHERE `评测日期` = ?',
      [today]
    );
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching evaluation:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期获取评价信息
router.get('/evaluation/:evaluationDate', async (req, res) => {
  try {
    const { evaluationDate } = req.params;
    const { 牌别, 批次 } = req.query;
    
    let query = 'SELECT * FROM `评价表` WHERE `评测日期` = ?';
    const params = [evaluationDate];
    
    if (牌别 && 批次) {
      query += ' AND `牌别` = ? AND `批次` = ?';
      params.push(牌别, 批次);
    }
    
    const [rows] = await pool.execute(query, params);
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching evaluation:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 更新评价表
router.put('/evaluation', async (req, res) => {
  try {
    const { 评测日期, 牌别, 批次, 风格特征, 香气特征, 烟气特征, 口感特征, 特殊质量, 结论 } = req.body;
    
    await pool.execute(
      `UPDATE \`评价表\` SET 
        \`风格特征\` = ?, 
        \`香气特征\` = ?, 
        \`烟气特征\` = ?, 
        \`口感特征\` = ?, 
        \`特殊质量\` = ?, 
        \`结论\` = ? 
      WHERE \`评测日期\` = ? AND \`牌别\` = ? AND \`批次\` = ?`,
      [风格特征, 香气特征, 烟气特征, 口感特征, 特殊质量, 结论, 评测日期, 牌别, 批次]
    );

    res.json({ success: true, message: '评价信息更新成功' });
  } catch (error) {
    console.error('Error updating evaluation:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 获取当天的缺陷描述
router.get('/defect/today', async (req, res) => {
  try {
    const today = new Date().toISOString().split('T')[0];
    const [rows] = await pool.execute(
      'SELECT * FROM `缺陷描述` WHERE `评测日期` = ?',
      [today]
    );
    res.json({ success: true, data: rows.length > 0 ? rows[0] : null });
  } catch (error) {
    console.error('Error fetching defect:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 根据评测日期获取缺陷描述
router.get('/defect/:evaluationDate', async (req, res) => {
  try {
    const { evaluationDate } = req.params;
    const [rows] = await pool.execute(
      'SELECT * FROM `缺陷描述` WHERE `评测日期` = ?',
      [evaluationDate]
    );
    res.json({ success: true, data: rows.length > 0 ? rows[0] : null });
  } catch (error) {
    console.error('Error fetching defect:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

// 创建或更新缺陷描述
router.post('/defect', async (req, res) => {
  try {
    const { 评测日期, 盒装缺陷, 卷制缺陷 } = req.body;
    const final评测日期 = 评测日期 || new Date().toISOString().split('T')[0];

    // 先尝试更新
    const [updateResult] = await pool.execute(
      'UPDATE `缺陷描述` SET `盒装缺陷` = ?, `卷制缺陷` = ? WHERE `评测日期` = ?',
      [盒装缺陷, 卷制缺陷, final评测日期]
    );

    // 如果没有更新任何行，则插入新记录
    if (updateResult.affectedRows === 0) {
      await pool.execute(
        'INSERT INTO `缺陷描述` (`评测日期`, `盒装缺陷`, `卷制缺陷`) VALUES (?, ?, ?)',
        [final评测日期, 盒装缺陷, 卷制缺陷]
      );
    }

    res.json({ success: true, message: '缺陷描述保存成功' });
  } catch (error) {
    console.error('Error saving defect:', error);
    res.status(500).json({ success: false, message: error.message });
  }
});

module.exports = router;

